<?php
/** 
 * EGP Framework
 *
 * LICENSE
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 *
 * @author     Akon(番茄红了) <aultoale@gmail.com>
 * @copyright  Copyright (c) 2008 (http://www.tblog.com.cn)
 * @license    http://www.gnu.org/licenses/gpl.html     GPL 3
 */

!defined('EGP_LIB_DIR') && die('Access Deny!');
!defined('CP_UTF8') && define('CP_UTF8', 65001);
/**
 * MS Access 数据库驱动
 *
 * @package    classes
 * @author     Akon(番茄红了) <aultoale@gmail.com>
 * @copyright  Copyright (c) 2008 (http://www.tblog.com.cn)
 * @license    http://www.gnu.org/licenses/gpl.html     GPL 3
 */
class EGP_Db_Adapter_Access extends EGP_Db_Adapter_Abstract
{

    /**
     * DCOM 服务器的名字 (默认为localhost)
     *
     * @var string
     */
    public static $SERVER_NAME = null;

    /**
     * 指定用于将 PHP 字符串（php-strings）转换成 UNICODE 字符串（unicode-strings）的代码页
     *
     * @var string
     */
    public static $CODEPAGE = CP_UTF8;

    /**
     * 最后一次插入数据的表名
     *
     * @var string
     */
    protected $_lastInsertTable = null;


    /**
     * 构造方法
     *
     * @param  array  $options  数据库连接配置信息
     * @throws EGP_Exception
     */
    public function __construct(array $options)
    {
        if ((substr(PHP_OS, 0, 3) != "WIN"))
            throw new EGP_Exception(__CLASS__ . ' 无法运行在 Windows 以外的操作系统');

        if (!class_exists('COM', false))
            throw new EGP_Exception(__CLASS__ . ' 无法加载 COM 组件');

        if (!isset($options['dsn']))
            throw new EGP_Exception('Access 连接配置缺少连接字串 (dsn)');

        $this->_options = $options;
    }


    /**
     * 获取 COM 组件实例
     *
     * @param  string  $moduleName
     * @return COM
     * @throws EGP_Exception
     */
    public function getCOMInstance($moduleName)
    {
        $instance = new COM($moduleName, self::$SERVER_NAME, self::$CODEPAGE);
        if (!$instance)
           throw new EGP_Exception($moduleName . ' 连接失败');
        return $instance;
    }

    /**
     * 根据配置信息连接到 MySQL 数据库
     */
    protected function _connect()
    {
        if ($this->_connection)
            return;
        $this->_connection = $this->getCOMInstance('ADODB.Connection');
        $this->_connection->open($this->_options['dsn']);
    }

    /**
     * 获取 ADODB.RecordSet
     *
     * @return COM
     */
    public function getRecordSet()
    {
        return $this->getCOMInstance('ADODB.RecordSet');
    }

    /**
     * 返回不支持方法的信息
     *
     * @param  string  $method
     * @return string
     */
    protected function _noSupportMessage($method)
    {
        return __CLASS__ . "不支持 '$method' 操作";
    }

    /**
     * 打开指定的数据库
     *
     * @param  string  $dbname  数据库名称
     * @return void
     * @throws EGP_Exception
     */
    public function selectDb($dbname)
    {
        throw new $this->_noSupportMessage(__FUNCTION__);
    }

    /**
     * 关闭数据库连接
     */
    protected function _close()
    {
        $this->getConnection()->close();
    }

    /**
     * 返回上一个操作中的错误信息的数字编码
     *
     * @return void
     * @throws EGP_Exception
     */
    public function errno()
    {
        throw new $this->_noSupportMessage(__FUNCTION__);
    }

    /**
     * 返回上一个操作产生的文本错误信息
     *
     * @return void
     * @throws EGP_Exception
     */
    public function error()
    {
        throw new $this->_noSupportMessage(__FUNCTION__);
    }

    /**
     * 执行一条查询语句
     *
     * @param  string  $sql
     * @return resource
     */
    protected function _query($sql)
    {
        $this->_lastSql = $sql;
        return $this->getConnection()->execute($sql);
    }

    /**
     * 插入数据到指定表中
     *
     * @param  string  $table   表名
     * @param  array   $data    需要插入的数据
     * @param  array   $schema  方案(数据库)名
     * @return integer          影响到的记录行数
     */
    public function insert($table, array $data, $schema = null)
    {
        foreach ($data as $key => $val) {
            $cols[] = $this->quoteAs($key);
            $vals[] = $this->quote($val);
        }
        $columns = '(' . implode(', ', $cols) . ')';
        $values = '(' . implode(', ', array_values($vals)) . ')';
        $sql = 'INSERT INTO ' . $this->quoteAs($this->quotePrefix($table)) .
               "\n\t$columns\nVALUES \n\t$values;\n";
        $this->query($sql);
        return 1;
    }

    /**
     * 更新指定表的数据
     *
     * @param  string  $table   表名
     * @param  array   $data    更新后的数据
     * @param  mixed   $where   更新条件
     * @param  array   $schema  方案(数据库)名
     * @return integer          影响到的记录行数
     */
    public function update($table, array $data, $where = '', $schema = null)
    {
        $sql = "SELECT COUNT(*) FROM \n\t" .
               $this->quoteAs($this->quotePrefix($table)) .
               ($where ? "\nWHERE $where" : '');
        $count = $this->fetchOne($sql);

        foreach ($data as $col => $val)
            $set[] = $this->quoteAs($col) . ' = ' . $this->quote($val);
        $where = $this->_whereExpr($where);
        $sql = "UPDATE " . $this->quoteAs($this->quotePrefix($table)) .
               " SET\n\t" . implode(", \n\t", $set) .
               ($where ? "\nWHERE $where" : '');
        $this->query($sql);

        $this->_lastInsertTable = $table;

        return $count;
    }

    /**
     * 删除指定表中的数据
     *
     * @param  string  $table   表名
     * @param  mixed   $where   删除条件
     * @param  array   $schema  方案(数据库)名
     * @return integer          影响到的记录行数
     */
    public function delete($table, $where = '', $schema = null)
    {
        $sql = "SELECT COUNT(*) FROM \n\t" .
               $this->quoteAs($this->quotePrefix($table)) .
               ($where ? "\nWHERE $where" : '');
        $count = $this->fetchOne($sql);

        $sql = "DELETE FROM " . $this->quoteAs($this->quotePrefix($table)) .
               ($where ? " WHERE " . $this->_whereExpr($where) : '');
        $this->query($sql);

        return $count;
    }

    /**
     * 添加 SQL 语句 LIMIT 限制
     *
     * @param  string   $sql
     * @param  integer  $count
     * @param  integer  $offset
     * @return string
     * @throws EGP_Exception
     */
    public function limit($sql, $count, $offset = 0)
    {
        $count = intval($count);
        if ($count <= 0)
            throw new EGP_Exception("无效的 LIMIT 参数 'count=$count'");
        $offset = intval($offset);
        if ($offset < 0)
            throw new EGP_Exception("无效的 LIMIT 参数 'offset=$offset'");

        $orderby = stristr($sql, 'ORDER BY');
        if ($orderby !== false) {
            $sort = (stripos($orderby, ' desc') !== false) ? 'desc' : 'asc';
            $order = str_ireplace('ORDER BY', '', $orderby);
            $order = trim(preg_replace('/\bASC\b|\bDESC\b/i', '', $order));
        }

        $sql = preg_replace('/^SELECT\s/i', 'SELECT TOP ' . ($count + $offset) . ' ', $sql);

        $sql = 'SELECT * FROM (SELECT TOP ' . $count . ' * FROM (' . $sql . ') AS inner_tbl';
        if ($orderby !== false) {
            $sql .= ' ORDER BY ' . $order . ' ';
            $sql .= (stripos($sort, 'asc') !== false) ? 'DESC' : 'ASC';
        }
        $sql .= ') AS outer_tbl';
        if ($orderby !== false) {
            $sql .= ' ORDER BY ' . $order . ' ' . $sort;
        }

        return $sql;
    }

    /**
     * 锁定表
     *
     * @return void
     * @throws EGP_Exception
     */
    public function lock($table)
    {
        throw new $this->_noSupportMessage(__FUNCTION__);
    }

    /**
     * 解除表锁定
     *
     * @param  string  $table
     * @return void
     * @throws EGP_Exception
     */
    public function unlock($table = null)
    {
        throw new $this->_noSupportMessage(__FUNCTION__);
    }

    /**
     * 开启事务处理
     *
     * @return void
     * @throws EGP_Exception
     */
    public function beginTransaction()
    {
        throw new $this->_noSupportMessage(__FUNCTION__);
    }

    /**
     * 提交事务
     *
     * @return void
     * @throws EGP_Exception
     */
    public function commit()
    {
        throw new $this->_noSupportMessage(__FUNCTION__);
    }

    /**
     * 回滚事务
     *
     * @return void
     * @throws EGP_Exception
     */
    public function rollback()
    {
        throw new $this->_noSupportMessage(__FUNCTION__);
    }

    /**
     * 根据查询取出所有资料
     *
     * @param  string  $sql
     * @return array
     */
    public function fetchAll($sql)
    {
        $rs = $this->query($sql);
        $data = array();
        while (!$rs->eof) {
            $row = array();
            for ($col = 0; $col < $rs->fields->count; $col++) {
                $row[$rs->fields[$col]->name] = $rs->fields[$col]->value;
            }
            $data[] = $row;
            $rs->moveNext();
        }
        $rs->close();
        return $data;
    }

    /**
     * 根据查询取出所有数据，并作为关联数组及数字数组返回
     *
     * @param  string  $sql
     * @return array
     */
    public function fetchArray($sql)
    {
        $rs = $this->query($sql);
        $data = array();
        while (!$rs->eof) {
            $row = array();
            for ($col = 0; $col < $rs->fields->count; $col++) {
                $row[$col] = $rs->fields[$col]->value;
                $row[$rs->fields[$col]->name] = $rs->fields[$col]->value;
            }
            $data[] = $row;
            $rs->moveNext();
        }
        $rs->close();
        return $data;
    }

    /**
     * 根据查询取出所有数据，并作为数组对像返回
     *
     * @param  string  $sql
     * @return array
     */
    public function fetchObj($sql)
    {
        $rs = $this->query($sql);
        $data = array();
        while (!$rs->eof) {
            $row = new StdClass();
            for ($col = 0; $col < $rs->fields->count; $col++) {
                $name = $rs->fields[$col]->name;
                $row->$name = $rs->fields[$col]->value;
            }
            $data[] = $row;
            $rs->moveNext();
        }
        $rs->close();
        return $data;
    }

    /**
     * 根据查询取出一行数据，并以为数组返回
     *
     * @param  string  $sql
     * @return array
     */
    public function fetchRow($sql)
    {
        $rs = $this->query($sql);
        $row = false;
        if ($rs->fields->count > 0) {
            for ($col = 0; $col < $rs->fields->count; $col++)
                $row[$rs->fields[$col]->name] = $rs->fields[$col]->value;
        }
        $rs->close();
        return $row;
    }

    /**
     * 根据查询取出第一行数据，并返回第一个字段的值
     *
     * @param  string  $sql
     * @return mixed
     */
    public function fetchOne($sql)
    {
        return $this->result($sql, 0);
    }

    /**
     * 取得结果集中指定列的内容
     *
     * @param  string   $sql
     * @param  integer  $row
     * @return mixed
     */
    public function result($sql, $row)
    {
        $rs = $this->query($sql);
        try {
            $result = $rs->fields[$row]->value;
        } catch (Exception $e) {
            $result = false;
        }
        $rs->close();
        return $result;
    }

    /**
     * 取得上一次 MySQL 操作所影响的记录行数
     *
     * @return void
     * @throws EGP_Exception
     */
    public function rowCount()
    {
        throw new $this->_noSupportMessage(__FUNCTION__);
    }

    /**
     * 取得结果集中行的数目
     *
     * @param  string  $sql
     * @return integer
     */
    public function numRows($sql)
    {
        $rs = $this->getRecordSet();
        $rs->open($sql, $this->getConnection(), 1 ,1);
        $count = $rs->RecordCount;
        $rs->close();
        return $count;
    }

    /**
     * 获取主键字段名称
     *
     * <font color=red><b>警告：</b>
     *     该方法获取到的主键字段名称可能会不准确
     *     规则为获取指定表第一个数字类型字段，且以ID为后缀命名的字段
     * </font>
     *
     * @param  string  $table
     * @return string
     */
    protected function _getPrimaryFieldName($table)
    {
        $rs = $this->select()->from($table, new EGP_Db_Expr('TOP 1 *'))->query();
        while (!$rs->eof) {
            for ($col = 0; $col < $rs->fields->count; $col++) {
                if ($rs->fields[$col]->type == 3 && preg_match('/id$/i', $rs->fields[$col]->name))
                    return $rs->fields[$col]->name;
            }
            $rs->moveNext();
        }
        $rs->close();
        return null;
    }

    /**
     * 取得上一次 INSERT 操作产生的主键 ID 值
     *
     * @return integer
     */
    public function lastInsertId()
    {
        if (null === $this->_lastInsertTable)
            return 0;

        $primaryFieldName = $this->_getPrimaryFieldName($this->_lastInsertTable);
        if (null === $primaryFieldName)
            return 0;

        $select = $this->select()->from(
            $this->_lastInsertTable,
            new EGP_Db_Expr('MAX(' . $this->quoteIdent($primaryFieldName) . ')')
        );
        return $this->fetchOne($select);
    }

    /**
     * 添加识别引用符
     *
     * @param  string   $value
     * @param  boolean  $auto
     * @return string
     */
    protected function _quoteIdent($value, $auto = false)
    {
        if ($value == '*' || $auto) {
            return $value;
        } else {
            $value = str_replace(' [', ' \[', $value);
            $value = str_replace('] ', '\] ', $value);
            return '[' . $value . ']';
        }
    }

}